package com.foosun.core.util;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class PoiUtils {

    static SimpleDateFormat DATE_FORMAT_YYYY_MM_DD = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static DecimalFormat df = new DecimalFormat("0");

    public static List<List<TreeMap<String, String>>> readExcel(String path) throws IOException {
        InputStream in = new FileInputStream(new File(path));
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        List<List<TreeMap<String, String>>> sheetList = new ArrayList<List<TreeMap<String, String>>>();// 取得整个xls各个sheet中的总数据
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            XSSFWorkbook xwb = new XSSFWorkbook(in);
            int sheetNumber = xwb.getNumberOfSheets();
            for (int i = 0; i < sheetNumber; i++) {

                XSSFSheet xssfSheet = xwb.getSheetAt(i);    //依赖于POI的Excel读取封装,读取Excel表格
                Iterator<Row> rowIterator = xssfSheet.iterator();

                List<TreeMap<String, String>> list = new ArrayList<TreeMap<String, String>>();// 取得一个sheet中n行的总记录，里面是每行的记录
                TreeMap<String, String> sheetName = new TreeMap<String, String>();
                sheetName.put(String.valueOf(i), xssfSheet.getSheetName());
                list.add(sheetName);


                int r = 0;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.iterator();
                    TreeMap<String, String> map = new TreeMap<String, String>();
                    int c = 0;
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String v = getCellValueSimple(cell);
                        map.put(i + "-" + r + "-" + c, v);
                        c++;
                    }
                    list.add(map);
                    r++;
                }
                sheetList.add(list);
            }
        }

        return sheetList;
    }

    //无公式的格式
    public static String getCellValueSimple(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    return DATE_FORMAT_YYYY_MM_DD.format(d);
                }
                return df.format(cell.getNumericCellValue());

            default:
                return cell.getStringCellValue();
        }
    }

}
